package com.dotflat.xj.repository.impl;

import com.dotflat.xj.po.TargetValueRecordAreaPO;
import com.dotflat.xj.repository.TargetRecordV2Repository;
import com.matt.commons.exception.BusinessException;
import com.matt.foundation.DO.ClassListDO;
import com.matt.foundation.model.ErrorCode;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.tuo51.evaluate.repositories.evaluatev2.EvaluateNativeRepository;
import com.tuo51.foudation.utils.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;
import java.util.stream.Stream;


@Repository
public class TargetRecordV2RepositoryImpl extends BaseNativeSqlRepository implements TargetRecordV2Repository {

    @Override
    public List<TargetValueRecordAreaPO> getAreaCountResult(String areaId, String startTime, String endTime, String type,Integer pageNum,Integer pageSize) {
        String findTeacherSql = "SELECT  tr.id, concat(t.typeName,'-',tr.time,'-',tc.name) as name,TRUNCATE(IFNULL((tr.recordCount/t.projectCount)*100,0),2) as scoreRate, \n" +
                "concat(tr.recordNumber,'/',t.projectNumber) as projectNumber , concat(tr.recordCount,'/',t.projectCount) as projectCount ,\n" +
                " tr.userName as createBy,tr.created ,tr.userName as checkBy,tr.created as checkDate" +
                " FROM `xj_target_record` tr LEFT JOIN xj_target t on tr.targetId = t.id  LEFT JOIN xj_target_class tc on tc.id = tr.targetClass \n" +
                "WHERE t.status = 0 AND tc.`name` is not null  AND tr.areaId = '" + areaId + "'  "+
                (StringUtils.isNotBlank(type) ? String.format(" AND t.typeName = \"%s\" ", type) : "") +
                 "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S') " +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S') " +
                "  LIMIT  "+pageNum+" ,"+pageSize+"" ;

        List<TargetValueRecordAreaPO> objectArrayList = sqlObjectList(findTeacherSql, TargetValueRecordAreaPO.class);
        return objectArrayList;
    }

    @Override
    public Integer getAreaCountResult(String areaId, String startTime, String endTime, String type) {
          String findTeacherSql = "SELECT  tr.id " +
                " FROM `xj_target_record` tr LEFT JOIN xj_target t on tr.targetId = t.id  LEFT JOIN xj_target_class tc on tc.id = tr.targetClass \n" +
                "WHERE  t.status = 0 AND  tc.`name` is not null  AND tr.areaId = '" + areaId + "'  "+
                (StringUtils.isNotBlank(type) ? String.format(" AND t.typeName = \"%s\" ", type) : "") +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S') " +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S') " +
                "  " ;
        List<TargetValueRecordAreaPO> objectArrayList = sqlObjectList(findTeacherSql, TargetValueRecordAreaPO.class);
        return objectArrayList.size();
    }

    @Override
    public Integer getAreaCountResult(String areaId, String startTime, String endTime, String type, String targetClassId,String targetId) {
        String findTeacherSql = "SELECT  tr.id " +
                " FROM `xj_target_record` tr LEFT JOIN xj_target t on tr.targetId = t.id  LEFT JOIN xj_target_class tc on tc.id = tr.targetClass \n" +
                "WHERE  t.status = 0 AND  tc.`name` is not null  AND tr.areaId = '" + areaId + "'  " +
                " AND  tr.targetClass =  '" + targetClassId + "' "+
                " AND  tr.targetId =  '" + targetId + "'  "+
                (StringUtils.isNotBlank(type) ? String.format(" AND t.typeName = \"%s\" ", type) : "") +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S') " +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S') " +
                "  " ;
        List<TargetValueRecordAreaPO> objectArrayList = sqlObjectList(findTeacherSql, TargetValueRecordAreaPO.class);
        return objectArrayList.size();
    }

    @Override
    public List<Object[]> typeNameCount(String ticket, String areaId, String startTime, String endTime) {
        String sql = "SELECT  t.typeName, IFNULL(SUM(tr.recordNumber),0) recordNumber ,IFNULL(SUM(t.projectCount),0) projectCount ,\n" +
                " IFNULL(SUM(tr.recordCount),0) recordCount , IFNULL(DATE_FORMAT(max(tr.created),'%Y-%m-%d') ,'') \n" +
                ",IFNULL(max(a.recordSortValue),0),IFNULL(min(a.recordSortValue),0) , IFNULL(FLOOR(AVG(a.recordSortValue)),0)  \n" +
                "  FROM  xj_target t LEFT JOIN  xj_target_record  tr on t.id = tr.targetId  LEFT JOIN \n" +
                " (SELECT  tr.id ,IFNULL(SUM(tvr.recordSortValue),0) recordSortValue " +
                " FROM  xj_target t LEFT JOIN  xj_target_record  tr on t.id = tr.targetId \n" +
                "  LEFT JOIN xj_target_value_record tvr  on tr.id  = tvr.recordId " +
                " WHERE  t.status= 0  GROUP BY  tr.id  ) a  on tr.id = a.id \n" +
                " WHERE  t.status= 0 " +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S') " +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S')" +
                "   AND  tr.areaId = '" + areaId + "' " +
                "  GROUP BY t.typeName ORDER BY tr.created desc ";
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> areaTypeCount(String areaId, String startTime, String endTime) {
        String sql = "SELECT  t.sort, IFNULL(SUM(tr.recordNumber),0) recordNumber ,IFNULL(SUM(t.projectCount),0) projectCount ,\n" +
                " IFNULL(SUM(tr.recordCount),0) recordCount , IFNULL(DATE_FORMAT(max(tr.created),'%Y-%m-%d') ,'') \n" +
                ",IFNULL(max(a.recordSortValue),0),IFNULL(min(a.recordSortValue),0) , IFNULL(FLOOR(AVG(a.recordSortValue)),0)  \n" +
                "  FROM  xj_target t LEFT JOIN  xj_target_record  tr on t.id = tr.targetId  LEFT JOIN \n" +
                " (SELECT  tr.id ,IFNULL(SUM(tvr.recordSortValue),0) recordSortValue " +
                " FROM  xj_target t LEFT JOIN  xj_target_record  tr on t.id = tr.targetId \n" +
                "  LEFT JOIN xj_target_value_record tvr  on tr.id  = tvr.recordId " +
                " WHERE  t.status= 0  GROUP BY  tr.id  ) a  on tr.id = a.id \n" +
                " WHERE  t.status= 0 " +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S') " +
                "   AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S')" +
                "   AND  tr.areaId = '" + areaId + "' " +
                "  GROUP BY t.sort ORDER BY tr.created desc ";
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> countResultDetails(String recordId) {
        String sql = "SELECT t.conclusionType,tvr.sortName,tvr.recordSortValue FROM xj_target_value_record tvr  " +
                " LEFT JOIN xj_target t on t.id  = tvr.targetId WHERE t.status= 0 and tvr.recordId = '"+recordId+"'";
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> countResult(String id, String startTime,String endTime, String type) {
        String sql = "SELECT  t.targetType, IFNULL(tr.recordNumber,0) recordNumber , IFNULL(tr.recordCount,0) recordCount , " +
                "IFNULL(t.projectCount,0) projectCount ,IFNULL(t.projectNumber,0) projectNumber , " +
                "tr.created ,tr.userName,tr.time ,tr.id  FROM  xj_target_record tr LEFT JOIN  xj_target t   on t.id = tr.targetId " +
                " WHERE  t.status= 0  and  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S')  " +
                " AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S') ";

        sql += String.format(" and tr.areaId  ='%s' ", id);

        if (Objects.nonNull(type)) {
            sql += String.format(" and t.typeName  ='%s' ", type);
        }
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> countResultArea(String areaId, String startTime, String endTime, String type) {
        String sql = "SELECT  t.targetType, IFNULL(tr.recordNumber,0) recordNumber , IFNULL(tr.recordCount,0) recordCount , " +
                "IFNULL(t.projectCount,0) projectCount ,IFNULL(t.projectNumber,0) projectNumber , " +
                "tr.created ,tr.userName,tr.time ,tr.id  FROM  xj_target_record tr LEFT JOIN  xj_target t   on t.id = tr.targetId " +
                " WHERE  t.status= 0  and  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S')  " +
                " AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S') ";

        sql += String.format(" and tr.areaId  ='%s' ", areaId);

        if (Objects.nonNull(type)) {
            sql += String.format(" and t.typeName  ='%s' ", type);
        }
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> groupTotalCount(String id, String startTime, String endTime) {
        String sql = "SELECT    IFNULL(sum(tr.recordNumber),0) recordNumber ,IFNULL(min(tr.recordCount),0) projectCount , IFNULL(max(tr.recordCount),0) recordCount ,IFNULL(max(tr.created),'')  " +
                " FROM   xj_target_record  tr LEFT JOIN xj_target t  on t.id = tr.targetId" +
                " WHERE  t.status= 0  and  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S')  " +
                " AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S') ";
        sql += String.format(" and tr.areaId  ='%s' ", id);
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

    @Override
    public List<Object[]> totalCount(String id, String startTime, String endTime, String sort) {
        String sql = "SELECT  t.targetType, IFNULL(tr.recordNumber,0) recordNumber ,IFNULL(t.projectCount,0) projectCount , IFNULL(tr.recordCount,0) recordCount , IFNULL(DATE_FORMAT(tr.created,'%Y-%m-%d') ,'') " +
                ", IFNULL(tr.targetId ,'')  " +
                " FROM  xj_target t LEFT JOIN  xj_target_record  tr on t.id = tr.targetId" +
                " WHERE  t.status= 0  and DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') >= DATE_FORMAT('" + startTime + "','%Y-%m-%d %H:%i:%S')  " +
                " AND  DATE_FORMAT(tr.created,'%Y-%m-%d %H:%i:%S') <= DATE_FORMAT('" + endTime + "','%Y-%m-%d %H:%i:%S') ";
        sql += String.format(" and tr.areaId  ='%s' ", id);
        if (Objects.nonNull(sort)) {
            sql += String.format(" and t.sort  ='%s' ", sort);
        }
        List<Object[]> objects = sqlArrayList(sql);
        return objects;
    }

}
